package com.jimet.repository;


import java.sql.*;
import java.util.Date;

import javax.sql.DataSource;
import javax.naming.Context;
import javax.naming.InitialContext;

import com.jimet.repository.RepConDef;






public class JimetRepositoryConnection {

	private String DBMS_VENDOR = RepConDef.getString("JimetRepConDef.DBMS_VENDOR"); //$NON-NLS-1$
	private String DBUSERNAME = RepConDef.getString("JimetRepConDef.DBUSERNAME"); //$NON-NLS-1$
	private String DBPASSWORD = RepConDef.getString("JimetRepConDef.DBPASSWORD"); //$NON-NLS-1$
	private String DBHOSTNAME = RepConDef.getString("JimetRepConDef.DBHOSTNAME"); //$NON-NLS-1$
	private String DBPORT = RepConDef.getString("JimetRepConDef.DBPORT"); //$NON-NLS-1$
	private String DBNAME = RepConDef.getString("JimetRepConDef.DBNAME"); //$NON-NLS-1$
	private String JNDI = RepConDef.getString("JimetRepConDef.JNDI");
	private String SERVER_VENDOR = RepConDef.getString("JimetRepConDef.SERVER_VENDOR");
	public String name = "";
	protected Connection connection;
	private long conTime;
	public static long connectionNumber;
	//private String connectStr = RepConDef.getString("JimetRepConDef.DB_CON_STR_PREFIX")+ DBHOSTNAME + RepConDef.getString("JimetRepConDef.DB_HOSTNAME_PORT_SEPERATOR_STR") + DBPORT; //$NON-NLS-1$ //$NON-NLS-2$

	public JimetRepositoryConnection() throws Exception {
		Date tm = new Date();
		this.setConTime(tm.getTime());	
		connect();
	}

	public JimetRepositoryConnection(boolean connect) throws Exception {
		Date tm = new Date();
		this.setConTime(tm.getTime());	
	}
	
	public JimetRepositoryConnection(Connection conn) throws Exception {
		Date tm = new Date();
		this.setConTime(tm.getTime());
		this.connection = conn;
	}
	
	private void connect() throws Exception
	{

		if(DBMS_VENDOR.equalsIgnoreCase("MSSQL2K")) //$NON-NLS-1$
			connectToMSSQL2K();
		else if(DBMS_VENDOR.equalsIgnoreCase("ORCL10G")) //$NON-NLS-1$
			connectToORCL10G();
		else if(DBMS_VENDOR.equalsIgnoreCase("MYSQL")) //$NON-NLS-1$
			connectToMYSQL();
		else if (DBMS_VENDOR.equalsIgnoreCase("IBMDB2"))
			connectToIBMDB2();				
		else if (DBMS_VENDOR.equalsIgnoreCase("JNDI"))
			connectByDataSource();			
		else throw new Exception("Corrupt Repository Definition File (RepConDef)"); //$NON-NLS-1$
	
	}


	private void connectToMYSQL() throws Exception 
	{

	    try 
	    {
		    Class.forName(RepConDef.getString("JimetRepConDef.DB_JDBC_DRIVER_NAME")); //$NON-NLS-1$
    	}
		catch (Exception ex)
  		{
			throw new Exception("Jimet DB.10001 - com.mysql.jdbc.Driver can not be located by Jimet."); //$NON-NLS-1$
		}

    try {
    		String connectStr = RepConDef.getString("JimetRepConDef.DB_CON_STR_PREFIX")+ DBHOSTNAME + RepConDef.getString("JimetRepConDef.DB_HOSTNAME_PORT_SEPERATOR_STR") + DBPORT + "/" + DBNAME; //$NON-NLS-1$ //$NON-NLS-2$
    		connection = DriverManager.getConnection(connectStr,DBUSERNAME,DBPASSWORD);

    	}
		catch (Exception ex)
  		{
			ex.printStackTrace();
			throw new Exception("Jimet DB.10002 - can not get the connection for the repository database server"); //$NON-NLS-1$
		}



		
	}

	private void connectToMSSQL2K() throws Exception
	{

	    try {
		    Class.forName(RepConDef.getString("JimetRepConDef.DB_JDBC_DRIVER_NAME")); //$NON-NLS-1$
		    Class.forName("org.apache.commons.dbcp.PoolingDriver");
    	}
		catch (Exception ex)
  		{
			throw new Exception("Jimet DB.10001 -" + RepConDef.getString("JimetRepConDef.DB_JDBC_DRIVER_NAME") + "can not be located by Jimet."); //$NON-NLS-1$
		}

    try {
    	
		connection = DriverManager.getConnection("jdbc:apache:commons:dbcp:/jimetpool");

    	}
		catch (Exception ex)
  		{
			throw ex;
			//new Exception("Jimet DB.10002 - can not get the connection for the repository database server"); //$NON-NLS-1$
		}


	}
	
    private void connectToIBMDB2() throws Exception
    {
	    try {
		    Class.forName(RepConDef.getString("JimetRepConDef.DB_JDBC_DRIVER_NAME")); //$NON-NLS-1$
    	}
		catch (Exception ex)
  		{
			throw new Exception("Jimet DB.10001 - com.ibm.db2.jcc.DB2Driver can not be located by Jimet."); //$NON-NLS-1$
		}

		try {
    		String connectStr = RepConDef.getString("JimetRepConDef.DB_CON_STR_PREFIX")+ DBHOSTNAME + RepConDef.getString("JimetRepConDef.DB_HOSTNAME_PORT_SEPERATOR_STR") + DBPORT + "/" + DBNAME; //$NON-NLS-1$ //$NON-NLS-2$
    		connection = DriverManager.getConnection(connectStr,DBUSERNAME,DBPASSWORD);

    	}
		catch (Exception ex)
  		{   
			throw new Exception("Jimet DB.10002 - can not get the connection for the repository database server"); //$NON-NLS-1$
		}
    }

    public Connection getSingleConnectionIBMDB2() throws Exception
    {
	    try {
		    Class.forName(RepConDef.getString("JimetRepConDef.DB_JDBC_DRIVER_NAME")); //$NON-NLS-1$
    	}
		catch (Exception ex)
  		{
			throw new Exception("Jimet DB.10001 - com.ibm.db2.jcc.DB2Driver can not be located by Jimet."); //$NON-NLS-1$
		}

		try {
    		String connectStr = RepConDef.getString("JimetRepConDef.DB_CON_STR_PREFIX")+ DBHOSTNAME + RepConDef.getString("JimetRepConDef.DB_HOSTNAME_PORT_SEPERATOR_STR") + DBPORT + "/" + DBNAME; //$NON-NLS-1$ //$NON-NLS-2$
    		connectionNumber++;
    		return DriverManager.getConnection(connectStr,DBUSERNAME,DBPASSWORD);

    	}
		catch (Exception ex)
  		{   
			ex.printStackTrace();
			throw new Exception("Jimet DB.10002 - can not get the connection for the repository database server" + ex.getMessage()); //$NON-NLS-1$
		}
    }
    
	
    private void connectByDataSource() throws Exception
    {
		try{
			/*
			 * WebSphere JDBC Connection
			*/			
			if (SERVER_VENDOR.equals("IBMWAS")){
				InitialContext localCtx = new InitialContext();
				DataSource ds = (DataSource)localCtx.lookup(JNDI);
	    		connection  = ds.getConnection();				
			}
			 /* 
			 * Tomcat JDBC Connection			   
			 */   			
			else if (SERVER_VENDOR.equals("TOMCAT")){
				 InitialContext ctx = new InitialContext();
				 Context localCtx = (Context) ctx.lookup("java:comp/env");
				 connection  = ((DataSource)localCtx.lookup(JNDI)).getConnection();				
			}
		}
		catch(Exception e){
				throw new Exception("Jimet DB.10002 -original : " + JNDI + " : " + e.toString()); //$NON-NLS-1$
		}
		connectionNumber++;
    }
   
    /*
     * CONNECTION WITH NO POOLING
     */
    
	/*
	private void connectToIBMDB2() throws Exception
    {
	    try {
		    Class.forName(RepConDef.getString("JimetRepConDef.DB_JDBC_DRIVER_NAME")); //$NON-NLS-1$
    	}
		catch (Exception ex)
  		{
			throw new Exception("Jimet DB.10001 - com.ibm.db2.jcc.DB2Driver can not be located by Jimet."); //$NON-NLS-1$
		}

		try {
    		String connectStr = RepConDef.getString("JimetRepConDef.DB_CON_STR_PREFIX")+ DBHOSTNAME + RepConDef.getString("JimetRepConDef.DB_HOSTNAME_PORT_SEPERATOR_STR") + DBPORT + "/" + DBNAME; //$NON-NLS-1$ //$NON-NLS-2$
    		connection = DriverManager.getConnection(connectStr,DBUSERNAME,DBPASSWORD);

    	}
		catch (Exception ex)
  		{   
			throw new Exception("Jimet DB.10002 - can not get the connection for the repository database server"); //$NON-NLS-1$
		}
    }
	*/
	private void connectToORCL10G() throws Exception
	{
		throw new Exception("Not Yet Implemented By Jimet"); //$NON-NLS-1$
	}
	
	/** the exact Authentication takes place here, behind the web service.
	 * @param username
	 * @param passwordMD5
	 * @return
	 * @throws Exception
	 */
	public String authenticate(String username, String passwordMD5) throws Exception
	{
		
		ResultSet rs;
		PreparedStatement stmt;
			String queryStr = "SELECT USR_ID from USR_IDENT " +
					"WHERE USR_USERNAME = ? AND " +
					"USR_PASSWORD = ?";
			stmt = connection.prepareStatement(queryStr);
			stmt.setString(1,username);
			stmt.setString(2,passwordMD5);

			rs = stmt.executeQuery();

		if(rs.next())
			return rs.getString(1);
		else
			return "";	
	}
	
	public String authenticateAlt(String username, String passwordMD5) throws Exception
	{
		
		ResultSet rs;
		PreparedStatement stmt;
			String queryStr = "SELECT USR_ID from USR_IDENT " +
					"WHERE USR_USERNAME = ? AND " +
					"USR_PASSWORD = ?";
			stmt = connection.prepareStatement(queryStr);
			stmt.setString(1,username);
			stmt.setString(2,passwordMD5);

			rs = stmt.executeQuery();

		if(rs.next())
			return rs.getString(1);
		else
			return "";	
	}
	
	public String authenticateMP(String username) throws Exception
	{
		
		ResultSet rs;
		PreparedStatement stmt;
			String queryStr = "SELECT USR_ID from USR_IDENT " +
					"WHERE USR_USERNAME = ? ";
			stmt = connection.prepareStatement(queryStr);
			stmt.setString(1,username);

			rs = stmt.executeQuery();

		if(rs.next())
			return rs.getString(1);
		else
			return "";	
	}
	
	/**
	 * disconects from the db and release resources.
	 */
	public void disconnect()
	{
		try
		{
		connection.close();
		connectionNumber--;
		}catch(SQLException e)
		{
		// ignore this exception...
		}
	}

	protected void finalize() throws Throwable
	{
		this.disconnect();
		super.finalize();
	}

	public void setConTime(long conTime) {
		this.conTime = conTime;
	}

	public long getConTime() {
		return conTime;
	}


}
